***********************************************************************************
********"Macroprudential Regulation, Quantitative Easing, and Bank Lending"********
***********************************************************************************
* This file sets up a list of datasets related to trading/capital gain of/from 
* securities
***********************************************************************************
***********************************************************************************


*==================================================================================
**# Bookmark (input)
use $inputdirectory/INB_portfolio2019, replace

gen die=0
replace die=1 if inlist(staemitit,8,9,101,94,257,67,28,29,40,86,259,92,258,105,50,55,260,276,477,479,480,574,576,577,580,587,588,591,595,799,807,812,814,816,853,910,911,913,916,921,929,940,945,950,959,974,32)&inlist(setemi,100,704,770,173,706,708,121,120)&inlist(stanziabilita,23,24)&(monthtom>=15)&(monthtom<=375)&(monthtom!=.)&time==tm(2019m8)

order bankid time cod_isin book portfolio, first
keep bankid time cod_isin book portfolio die

rename cod_isin COD_ISIN

**# Bookmark (intermediate dataset: ISIN to get prices)
/*
keep COD_ISIN
drop if COD_ISIN==""
duplicates drop COD_ISIN, force
odbc exec ("drop table F671750.ISINBANKSQE"), dsn(INFOSTAT)
odbc exec("create table F671750.ISINBANKSQE (COD_ISIN  VARCHAR2(12))"), dsn("INFOSTAT") 
odbc insert COD_ISIN, table ("F671750.ISINBANKSQE") dsn(INFOSTAT) 
*clear all
*odbc load, exec ("select count(*) as var1 from F671750.ISINBANKSQE") dsn(INFOSTAT)
restore
*/

egen dielong=max(die), by(COD_ISIN)
keep if dielong==1
keep if portfolio==3|portfolio==2

collapse (sum) book, by(bankid time COD_ISIN)

merge m:1 bankid using $inputdirectory/INB_preann2019m8, keepus(e13)
keep if _merge==3|_merge==2
drop _merge
replace time=tm(2019m8) if time==.

fillin bankid COD_ISIN time
replace book=0 if book==.
egen isin=group(COD_ISIN)
drop if isin==.
egen id=group(bankid isin)
xtset id time

merge m:1 time COD_ISIN using $inputdirectory/INB_isinsprices2019
keep if _merge!=2
drop _merge
replace price=. if price==0
replace priceavg=. if priceavg==0

gen quantity=book/price
xtset id time
drop if priceavg==.
gen cgain=L.quantity*(price-L.price)
gen cgainU=cgain
replace cgainU=0 if sign(L.quantity)!=sign(quantity)
replace cgainU=quantity*(price-L.price) if (sign(L.quantity)==sign(quantity))&(abs(quantity)<abs(L.quantity))

collapse (sum) cgain cgainU, by(bankid time)

merge 1:1 bankid time using $inputdirectory/INB_assets2019, keepus(assets)
keep if _merge!=2
drop _merge
xtset bankid time
gen cgasover=100*cgain/L.assets
gen cgasoverU=100*cgainU/L.assets
keep cgasover* cgain* bankid time
keep if time>=tm(2019m3)

**# Bookmark (saving dataset - capital gain from QE-eligible securities not valued
* at historical cost)
compress
save $inputdirectory/INB_CGP322019, replace


*==================================================================================
**# Bookmark (input)
clear *
use $inputdirectory/INB_portfolio2019, replace

gen die=0
replace die=1 if inlist(staemitit,8,9,101,94,257,67,28,29,40,86,259,92,258,105,50,55,260,276,477,479,480,574,576,577,580,587,588,591,595,799,807,812,814,816,853,910,911,913,916,921,929,940,945,950,959,974,32)&inlist(setemi,100,704,770,173,706,708,121,120)&inlist(stanziabilita,23,24)&(monthtom>=15)&(monthtom<=375)&(monthtom!=.)&time==tm(2019m8)

xtset id time

order bankid time cod_isin book portfolio, first
keep bankid time cod_isin book portfolio die
rename cod_isin COD_ISIN

egen dielong=max(die), by(COD_ISIN)
keep if dielong==1
keep if portfolio==3|portfolio==2
drop portfolio

collapse (sum) book, by(bankid time COD_ISIN)

merge m:1 bankid using $inputdirectory/INB_preann2019m8, keepus(e13)
keep if _merge==3|_merge==2
drop _merge
replace time=tm(2019m8) if time==.

fillin bankid COD_ISIN time
replace book=0 if book==.
egen isin=group(COD_ISIN)
drop if isin==.
egen id=group(bankid isin)

merge m:1 time COD_ISIN using $inputdirectory/INB_isinsprices2019
keep if _merge!=2
drop _merge
replace price=. if price==0
replace priceavg=. if priceavg==0

merge m:1 bankid time using $inputdirectory/INB_assets2019, keepus(assets)
keep if _merge!=2
drop _merge

xtset id time
gen quantity=book/price
gen qsold=quantity-L.quantity
gen sales=(qsold)*priceavg
gen pps=200*(quantity-L.quantity)/(quantity+L.quantity)
keep if sales!=.

collapse (sum) sales (max) assets, by(bankid time)
xtset bankid time
gen tradingover=100*sales/L.assets
keep if time>=tm(2019m8)
keep tradingover sales bankid time
gen tradingoversd=tradingover/r(sd)
recode tradingoversd (.=0)

**# Bookmark (saving dataset - trading of QE-eligible securities in the trading and
* available-for-sale portfolio
compress
save $inputdirectory/INB_TP322019, replace


*==================================================================================
**# Bookmark (input)
clear *
use $inputdirectory/INB_portfolio2019, replace

gen die=0
replace die=1 if inlist(staemitit,8,9,101,94,257,67,28,29,40,86,259,92,258,105,50,55,260,276,477,479,480,574,576,577,580,587,588,591,595,799,807,812,814,816,853,910,911,913,916,921,929,940,945,950,959,974,32)&inlist(setemi,100,704,770,173,706,708,121,120)&inlist(stanziabilita,23,24)&(monthtom>=15)&(monthtom<=375)&(monthtom!=.)&time==tm(2019m8)

xtset id time

order bankid time cod_isin book portfolio, first
keep bankid time cod_isin book portfolio die
rename cod_isin COD_ISIN

egen dielong=max(die), by(COD_ISIN)
keep if dielong==1
keep if portfolio==3
drop portfolio

collapse (sum) book, by(bankid time COD_ISIN)

merge m:1 bankid using $inputdirectory/INB_preann2019m8, keepus(e13)
keep if _merge==3|_merge==2
drop _merge
replace time=tm(2019m8) if time==.

fillin bankid COD_ISIN time
replace book=0 if book==.
egen isin=group(COD_ISIN)
drop if isin==.
egen id=group(bankid isin)

merge m:1 time COD_ISIN using $inputdirectory/INB_isinsprices2019
keep if _merge!=2
drop _merge
replace price=. if price==0
replace priceavg=. if priceavg==0

merge m:1 bankid time using $inputdirectory/INB_assets2019, keepus(assets)
keep if _merge!=2
drop _merge

xtset id time
gen quantity=book/price
gen qsold=quantity-L.quantity
gen sales=(qsold)*priceavg
gen pps=200*(quantity-L.quantity)/(quantity+L.quantity)
keep if sales!=.

collapse (sum) sales (max) assets, by(bankid time)
xtset bankid time
gen tradingover=100*sales/L.assets
keep if time>=tm(2019m8)
keep tradingover sales bankid time
gen tradingoversd=tradingover/r(sd)
recode tradingoversd (.=0)

**# Bookmark (saving dataset - trading of QE-eligible securities in the 
* trading book)
compress
save $inputdirectory/INB_TP32019, replace


*==================================================================================
**# Bookmark (input)
clear *
use $inputdirectory/INB_portfolio2019, replace

gen die=0
replace die=1 if inlist(staemitit,8,9,101,94,257,67,28,29,40,86,259,92,258,105,50,55,260,276,477,479,480,574,576,577,580,587,588,591,595,799,807,812,814,816,853,910,911,913,916,921,929,940,945,950,959,974,32)&inlist(setemi,100,704,770,173,706,708,121,120)&inlist(stanziabilita,23,24)&(monthtom>=15)&(monthtom<=375)&(monthtom!=.)&time==tm(2019m8)

xtset id time

order bankid time cod_isin book portfolio, first
keep bankid time cod_isin book portfolio die
rename cod_isin COD_ISIN

egen dielong=max(die), by(COD_ISIN)
keep if dielong==1
keep if portfolio==2
drop portfolio

collapse (sum) book, by(bankid time COD_ISIN)

merge m:1 bankid using $inputdirectory/INB_preann2019m8, keepus(e13)
keep if _merge==3|_merge==2
drop _merge
replace time=tm(2019m8) if time==.

fillin bankid COD_ISIN time
replace book=0 if book==.
egen isin=group(COD_ISIN)
drop if isin==.
egen id=group(bankid isin)

merge m:1 time COD_ISIN using $inputdirectory/INB_isinsprices2019
keep if _merge!=2
drop _merge
replace price=. if price==0
replace priceavg=. if priceavg==0

merge m:1 bankid time using $inputdirectory/INB_assets2019, keepus(assets)
keep if _merge!=2
drop _merge

xtset id time
gen quantity=book/price
gen qsold=quantity-L.quantity
gen sales=(qsold)*priceavg
gen pps=200*(quantity-L.quantity)/(quantity+L.quantity)
keep if sales!=.

collapse (sum) sales (max) assets, by(bankid time)
xtset bankid time
gen tradingover=100*sales/L.assets
keep if time>=tm(2019m8)
keep tradingover sales bankid time
gen tradingoversd=tradingover/r(sd)
recode tradingoversd (.=0)

**# Bookmark (saving dataset - trading of QE-eligible securities in the 
* available-for-sale portfolio)
compress
save $inputdirectory/INB_TP22019, replace


*==================================================================================
**# Bookmark (input)
clear *
use $inputdirectory/INB_portfolio2019, replace

xtset id time

order bankid time cod_isin book portfolio, first
keep bankid time cod_isin book portfolio
rename cod_isin COD_ISIN

keep if portfolio==3|portfolio==2
drop portfolio

collapse (sum) book, by(bankid time COD_ISIN)

merge m:1 bankid using $inputdirectory/INB_preann2019m8, keepus(e13)
keep if _merge==3|_merge==2
drop _merge
replace time=tm(2019m8) if time==.

fillin bankid COD_ISIN time
replace book=0 if book==.
egen isin=group(COD_ISIN)
drop if isin==.
egen id=group(bankid isin)

merge m:1 time COD_ISIN using $inputdirectory/INB_isinsprices2019
keep if _merge!=2
drop _merge
replace price=. if price==0
replace priceavg=. if priceavg==0

merge m:1 bankid time using $inputdirectory/INB_assets2019, keepus(assets)
keep if _merge!=2
drop _merge

xtset id time
gen quantity=book/price
gen qsold=quantity-L.quantity
gen sales=(qsold)*priceavg
gen pps=200*(quantity-L.quantity)/(quantity+L.quantity)
keep if sales!=.

collapse (sum) sales (max) assets, by(bankid time)
xtset bankid time
gen tradingover=100*sales/L.assets
keep if time>=tm(2019m8)
keep tradingover sales bankid time
gen tradingoversd=tradingover/r(sd)
recode tradingoversd (.=0)

**# Bookmark (saving dataset - trading of all securites)
compress
save $inputdirectory/INB_TP32ALL2019, replace
